package com.app.tools;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 导出Excel
 */
public class Excel {
    /**
     * 文件导出方法
     * @param outputStream 输出流
     * @param headerStr    表头
     */
    public void exportExcel(OutputStream outputStream, String[] headerStr) throws IOException{
        //创建一个内存Excel对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一个表格
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        //获取表头
        HSSFRow headerRow = sheet.createRow(0);
        //设置列宽
        for (int i = 0; i < headerStr.length; i++) {
            sheet.setColumnWidth(i, 5000);
        }
        //设置头单元格样式
        HSSFCellStyle hssfStyle = workbook.createCellStyle();
        hssfStyle.setAlignment(HorizontalAlignment.CENTER);//设置居中
        HSSFFont headerFont = workbook.createFont(); //设置字体
        headerFont.setFontName("黑体");
        hssfStyle.setFont(headerFont);
        for (int i = 0; i < headerStr.length; i++) {
            //创建一个单元格
            HSSFCell hssfCell = headerRow.createCell(i);
            hssfCell.setCellStyle(hssfStyle);
            hssfCell.setCellValue(headerStr[i]);
        }
        //将内存创建的excel对象输出到文件中
        workbook.write(outputStream);
    }

    /**
     * 文件导入读取
     */
    public List<String[]> exParseExcel(InputStream inputStream, String suffix, int startRow) throws IOException {
        Workbook workbook = null;
        if(suffix.equals(".xls")){
            workbook = new HSSFWorkbook(inputStream);
        }else if(suffix.equals(".xlsx")){
            workbook = new XSSFWorkbook(inputStream);
        }else {
            return null;
        }
        Sheet sheet = workbook.getSheetAt(0);
        if(sheet==null){
            return null;
        }
        int lastRow = sheet.getLastRowNum();
        if(lastRow<=startRow){
            return null;
        }
        Row row=null;
        Cell cell =null;
        List<String[]> list = new ArrayList<>();
        for(int rowNum = startRow;rowNum<=lastRow;rowNum++){
            row = sheet.getRow(rowNum);
            short firstCellNum = row.getFirstCellNum();
            short lastCellNum = row.getLastCellNum();
            if(firstCellNum!=lastCellNum && lastCellNum!=0){
                String[] rowArray = new String[lastCellNum];
                for(int cellNum=firstCellNum;cellNum<lastCellNum;cellNum++){
                    cell = row.getCell(cellNum);
                    if(cell==null){
                        rowArray[cellNum]="";
                    }else {
                        rowArray[cellNum]=parseCell(cell);
                    }
                }
                list.add(rowArray);
            }
        }
        return list;
    }
    public String parseCell(Cell cell){
        String  value=null;
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:{
                value = String.valueOf(cell.getNumericCellValue());
                break;
            }
            case STRING:{
                value = cell.getStringCellValue();
                break;
            }
            case BOOLEAN:
            case FORMULA: {
                break;
            }
            default:
                break;
        }
        if(value.indexOf(".")>-1){
            value = value.substring(0,value.indexOf("."));
        }
        return value;
    }
}
